package com.camin.android.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "recipe_manager.db";
	private static final int DATABASE_VERSION = 3;

	public DataHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/**
	 * Create all structures in database (tables ) at helper creation
	 */
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + RecipeTable.TABLE_NAME + " ("
				+ RecipeTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ RecipeTable.recipeName + " TEXT, "
				+ RecipeTable.recipeCookingTime + " DECIMAL, "
				+ RecipeTable.recipeCookingInstructions + " TEXT, "
				+ RecipeTable.recipeServingInstructions + " TEXT )");

		db.execSQL("CREATE TABLE " + IngredientTable.TABLE_NAME + " ("
				+ IngredientTable._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ IngredientTable.ingredientName + " TEXT )");

		db.execSQL("CREATE TABLE " + RecipeIngredientTable.TABLE_NAME + " ("
				+ RecipeIngredientTable.recipeId + " INTEGER , "
				+ RecipeIngredientTable.ingredientId + " TEXT, "
				+ RecipeIngredientTable.measureUnit + " TEXT, "
				+ RecipeIngredientTable.quantity + " DECIMAL )");
	}

	/**
	 * Upgrade (drop then recreate ) all structures in database
	 */
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + RecipeTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + IngredientTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + RecipeIngredientTable.TABLE_NAME);

		onCreate(db);
	}

	/**
	 * Insert a recipe object in database
	 * 
	 * @param db
	 * @param recipeValues
	 * @return
	 */
	public long insertRecipe(SQLiteDatabase db, ContentValues recipeValues) {
		return db.insert(RecipeTable.TABLE_NAME, null, recipeValues);
	}

	/**
	 * Insert an ingredient object in database
	 * 
	 * @param db
	 * @param ingredientValues
	 * @return
	 */
	public long insertIngredient(SQLiteDatabase db,
			ContentValues ingredientValues) {
		return db.insert(IngredientTable.TABLE_NAME, null, ingredientValues);
	}

	/**
	 * Delete recipe
	 * 
	 * @param db
	 * @param recipeId
	 * @return
	 */
	public long deleteRecipe(SQLiteDatabase db, Long recipeId) {
		return db.delete(RecipeTable.TABLE_NAME, RecipeTable._ID + "=? ",
				new String[] { Long.toString(recipeId) });
	}

	/**
	 * Insert and recipeIngredient object in database
	 * 
	 * @param db
	 * @param recipeIngredientValues
	 * @return
	 */
	public long insertRecipeIngredient(SQLiteDatabase db,
			ContentValues recipeIngredientValues) {
		return db.insert(RecipeIngredientTable.TABLE_NAME, null,
				recipeIngredientValues);
	}

	/**
	 * Delete all ingredients for a recipe Id
	 * 
	 * @param db
	 * @param recipeId
	 * @return
	 */
	public long deleteRecipeIngredient(SQLiteDatabase db, Long recipeId) {
		return db.delete(RecipeIngredientTable.TABLE_NAME,
				RecipeIngredientTable.recipeId + "=? ",
				new String[] { Long.toString(recipeId) });
	}

	/**
	 * Update an ingredient object
	 * 
	 * @param db
	 * @param ingredientValues
	 * @param ingredientId
	 * @return
	 */
	public int updateIngredient(SQLiteDatabase db,
			ContentValues ingredientValues, Long ingredientId) {
		return db.update(IngredientTable.TABLE_NAME, ingredientValues,
				IngredientTable._ID + " = ?",
				new String[] { Long.toString(ingredientId) });
	}

	/**
	 * Update an recipe object
	 * 
	 * @param db
	 * @param recipeValues
	 * @param recipeId
	 * @return
	 */
	public int updateRecipe(SQLiteDatabase db, ContentValues recipeValues,
			Long recipeId) {
		return db.update(RecipeTable.TABLE_NAME, recipeValues, RecipeTable._ID
				+ " = ?", new String[] { Long.toString(recipeId) });
	}

	/**
	 * Delete and ingredient object from database
	 * 
	 * @param db
	 * @param ingredientId
	 * @return
	 */
	public long deleteIngredient(SQLiteDatabase db, Long ingredientId) {
		return db.delete(IngredientTable.TABLE_NAME, IngredientTable._ID
				+ "=? ", new String[] { Long.toString(ingredientId) });
	}

	/**
	 * Get all recipes
	 * 
	 * @param db
	 * @return {@link Cursor}
	 */
	public Cursor getAllRecipes(SQLiteDatabase db) {
		Cursor cur = db.rawQuery("SELECT " + RecipeTable._ID + " as _id, "
				+ RecipeTable.recipeName + " from " + RecipeTable.TABLE_NAME,
				new String[] {});
		return cur;
	}

	/**
	 * Get a list of recipes filtered by a recipeNameFilter
	 * 
	 * @param db
	 * @param recipeNameFilter
	 * @return {@link Cursor}
	 */
	public Cursor getRecipesListByRecipeName(SQLiteDatabase db, String recipeNameFilter) {
		Cursor cur = db.query(RecipeTable.TABLE_NAME, new String[] {
				RecipeTable._ID, RecipeTable.recipeName,
				RecipeTable.recipeCookingTime,
				RecipeTable.recipeCookingInstructions,
				RecipeTable.recipeServingInstructions }, RecipeTable.recipeName
				+ " like '" + recipeNameFilter + "%'", null, null, null, null);
		return cur;
	}

	/**
	 * Get all ingredients
	 * 
	 * @param db
	 * @return {@link Cursor}
	 */
	public Cursor getAllIngredients(SQLiteDatabase db) {
		Cursor cur = db.rawQuery("SELECT " + IngredientTable._ID + " as _id, "
				+ IngredientTable.ingredientName + " from "
				+ IngredientTable.TABLE_NAME, new String[] {});
		return cur;
	}

	/**
	 * Get a list of ingredients filtered by a ingredientNameFilter
	 * 
	 * @param db
	 * @param ingredientNameFilter
	 * @return {@link Cursor}
	 */
	public Cursor getIngredientsListByIngredientName(SQLiteDatabase db, String ingredientNameFilter) {
		Cursor cur = db.query(IngredientTable.TABLE_NAME, new String[] {
				IngredientTable._ID, IngredientTable.ingredientName },
				IngredientTable.ingredientName + " like '"
						+ ingredientNameFilter + "%'", null, null, null, null);
		return cur;
	}

	/**
	 * Returns a cursor with ingredients with id = ingredientId
	 * 
	 * @param db
	 * @param ingredientId
	 * @return
	 */
	public Cursor getIngredientById(SQLiteDatabase db, Long ingredientId) {
		Cursor cur = db.query(IngredientTable.TABLE_NAME, new String[] {
				IngredientTable._ID, IngredientTable.ingredientName },
				IngredientTable._ID + " = " + ingredientId, null, null, null,
				null);
		return cur;
	}

	/**
	 * Returns a cursor with recipes with id = recipeId
	 * 
	 * @param db
	 * @param recipeId
	 * @return
	 */
	public Cursor getRecipeById(SQLiteDatabase db, Long recipeId) {
		Cursor cur = db.query(RecipeTable.TABLE_NAME, new String[] {
				RecipeTable._ID, RecipeTable.recipeName,
				RecipeTable.recipeCookingInstructions,
				RecipeTable.recipeCookingTime,
				RecipeTable.recipeServingInstructions }, RecipeTable._ID
				+ " = " + recipeId, null, null, null, null);
		return cur;
	}

	/**
	 * Returns a cursor with recipes with id = recipeId
	 * 
	 * @param db
	 * @param recipeId
	 * @return
	 */
	public Cursor getRecipeIngredientsById(SQLiteDatabase db, Long recipeId) {
		Cursor cur = db.query(RecipeIngredientTable.TABLE_NAME, new String[] {
				RecipeIngredientTable.recipeId,
				RecipeIngredientTable.ingredientId,
				RecipeIngredientTable.quantity,
				RecipeIngredientTable.measureUnit }, RecipeIngredientTable.recipeId + " = "
				+ recipeId, null, null, null, null);
		return cur;
	}

}
